package idv.ben.blogspotAlerter
{
	import com.allurent.database.ConnectionPool;
	import com.allurent.database.StatementPool;
	
	import flash.data.SQLConnection;
	import flash.data.SQLMode;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.Event;
	import flash.events.EventDispatcher;
	import flash.filesystem.File;
	import flash.net.URLLoader;
	import flash.net.URLRequest;
	
	import idv.ben.blogspotAlerter.event.BlogspotEvent;
	import idv.ben.blogspotAlerter.event.BlogspotWndEvent;
	import idv.ben.blogspotAlerter.event.PostEvent;
	
	import mx.logging.ILogger;
	import mx.logging.Log;

	public class BlogspotAlerter extends EventDispatcher
	{
		private static var _myLogger:ILogger = Log.getLogger("idv.ben.blogspotAlerter.BlogspotAlerter");
		
		private var __databaseFile:File = File.applicationStorageDirectory.resolvePath("data.db");
		
		private var __connectionPool:ConnectionPool;
		private var __statementPool:StatementPool;
		
		public function BlogspotAlerter()
		{
			initDB();
		}
		
		private function initDB():void{
			if(!__databaseFile.exists){
				createDB();
			}
			connectDB();
		}
		
		private function createDB():void{
			var sqlConnection:SQLConnection = new SQLConnection();
			sqlConnection.open(__databaseFile, SQLMode.CREATE);
			
			for each (var statement:String in [CREATE_TBL_BLOGSPOT, CREATE_TBL_POST]){
				var sqlStatement:SQLStatement = new SQLStatement();
				sqlStatement.sqlConnection = sqlConnection;
				sqlStatement.text = statement;
				sqlStatement.execute();
			}
			
			sqlConnection.close();
		}

		private function connectDB():void{
			__connectionPool = new ConnectionPool(__databaseFile);
			__statementPool = new StatementPool(__connectionPool);
		}		
		
		public function addBlog(url:String):void{
			url += (url.substr(-1)=="/" ? "" : "/");
			
			var blogs:Array = getBlogsByLink(url);
			if(blogs!=null){
				dispatchEvent(new BlogspotWndEvent(BlogspotWndEvent.DUPLICATE_URL, url));
			}else{
				url += "feeds/posts/default?alt=rss";
				
				var loader:URLLoader = new URLLoader(new URLRequest(url));
				loader.addEventListener(Event.COMPLETE, loadBlogFeedComplete);
			}			
		}
		
		private function getBlogsByLink(link:String):Array{
			var data:Array;
			
			var conn:SQLConnection = new SQLConnection();
			conn.open(__databaseFile, SQLMode.READ);
			
			var cmd:SQLStatement = new SQLStatement();
			cmd.sqlConnection = conn;
			cmd.text = SEARCH_BLOGSPOT_BY_LINK;
            cmd.parameters[":link"] = link;
            cmd.execute();
            
            data = cmd.getResult().data;
            
            conn.close();
            
            return data;    
		}
		
		private function loadBlogFeedComplete(evtObj:Event):void{
			var feed:XML = new XML(URLLoader(evtObj.target).data);
			var atom:Namespace = feed.namespace("atom");
			var openSearch:Namespace = feed.namespace("openSearch");
			
			var obj:Object = new Object();
			obj.id = feed.channel.atom::id.toString();
			obj.lastBuildDate = feed.channel.lastBuildDate.toString();
			obj.title = feed.channel.title.toString();
			obj.description = feed.channel.description.toString();
			obj.link = feed.channel.link.toString();
			obj.managingEditor = feed.channel.managingEditor.toString();
			
			__statementPool.execute(INSERT_ROW_BLOGSPOT, obj, function(sqlResult:SQLResult):void{
				dispatchEvent(new BlogspotEvent(BlogspotEvent.BLOGSPOT_ADDED, obj));
			});
			
			updatePosts(feed.channel.item, false);
		}
		
		public function checkPostsUpdate(url:String):void{
			url += (url.substr(-1)=="/" ? "" : "/");
			url += "feeds/posts/default?alt=rss";
			
			var loader:URLLoader = new URLLoader(new URLRequest(url));
			loader.addEventListener(Event.COMPLETE, loadPostsFeedComplete);
		}
		
		private function loadPostsFeedComplete(evtObj:Event):void{
			var feed:XML = new XML(URLLoader(evtObj.target).data);
			var atom:Namespace = feed.namespace("atom");
			var openSearch:Namespace = feed.namespace("openSearch");
			
			updatePosts(feed.channel.item, true);
		}
		
		private function updatePosts(xmllist:XMLList, isShowEvent:Boolean):void{
			if(xmllist.length()>0){
				var item:XML = xmllist[0];
				
				var atom:Namespace = item.namespace("atom");
				var openSearch:Namespace = item.namespace("openSearch");
				
				var obj:Object = new Object();
				obj.guid = item.guid.toString();
				obj.pubDate = item.pubDate.toString();
				obj.updated = item.atom::updated.toString();
				obj.title = item.title.toString();
				obj.summary = item.atom::summary.toString();
				obj.link = item.link.toString();
				obj.author = item.author.toString();
				
				for(var j:String in obj){
					_myLogger.debug(j + "=" + obj[j]);
				}
				
				__statementPool.execute(SEARCH_BLOGSPOT_BY_GUID, {guid:obj.guid}, function(sqlResult:SQLResult):void{
					if(sqlResult.data==null || sqlResult.data.length==0){
						//新增
						__statementPool.execute(INSERT_ROW_POST, obj, function(sqlResult:SQLResult):void{});
						if(isShowEvent){
							dispatchEvent(new PostEvent(PostEvent.POST_ADDED, obj));
						}
					}else{
						//判斷更新
						if(sqlResult.data[0].updated!=obj.updated){
							//更新
							__statementPool.execute(UPDATE_ROW_POST, obj, function(sqlResult:SQLResult):void{});
							if(isShowEvent){
								dispatchEvent(new PostEvent(PostEvent.POST_UPDATED, obj));
							}
						}else{
							//不用更新
						}
					}
					
					//繼續判斷剩下的post
					delete xmllist[0];
					updatePosts(xmllist, isShowEvent);
				});
			}
		}
		
		public function getAllBlogs():Array{
			
			var data:Array;
			
			var conn:SQLConnection = new SQLConnection();
			conn.open(__databaseFile, SQLMode.READ);
			
			var cmd:SQLStatement = new SQLStatement();
			cmd.sqlConnection = conn;
			cmd.text = SEARCH_BLOGSPOT_ALL;
            cmd.execute();
            
            data = cmd.getResult().data.slice(0);
            
            conn.close();
            
            return data;    
		}
		
		
		private static const CREATE_TBL_BLOGSPOT:String =
			"CREATE TABLE blogspot (id TEXT PRIMARY KEY, lastBuildDate TEXT, title TEXT, description TEXT, link TEXT, managingEditor TEXT);";
		private static const CREATE_TBL_POST:String =
			"CREATE TABLE post (guid TEXT PRIMARY KEY, pubDate TEXT, updated TEXT, title TEXT, summary TEXT, link TEXT, author TEXT);";

		private static const INSERT_ROW_BLOGSPOT:String =
			"INSERT INTO blogspot (id, lastBuildDate, title, description, link, managingEditor) VALUES (:id, :lastBuildDate, :title, :description, :link, :managingEditor);";
		private static const INSERT_ROW_POST:String =
			"INSERT INTO post (guid, pubDate, updated, title, summary, link, author) VALUES (:guid, :pubDate, :updated, :title, :summary, :link, :author);";

		private static const UPDATE_ROW_POST:String =
			"UPDATE post SET pubDate = :pubDate, updated = :updated, title = :title, summary = :summary, link = :link, author = :author WHERE guid = :guid;";

		private static const SEARCH_BLOGSPOT_ALL:String =
			"SELECT * FROM blogspot";
			
		private static const SEARCH_BLOGSPOT_BY_LINK:String =
			"SELECT * FROM blogspot WHERE link = :link";
		private static const SEARCH_BLOGSPOT_BY_GUID:String =
			"SELECT * FROM post WHERE guid = :guid";
	}
}